/*##############################################################################
 * 文件：SqliteDB.cpp
 * 描述：对应头文件的实现
 * 作者：hickman
 * 时间：2016年12月15日
 * 版权：Copyright(C) 2016 Fsmeeting.com. All rights reserved.
 #############################################################################*/

// sqlite3.3.1后貌似是线程安全的

#include "SqliteDB.h"
#include "StrUtil.h"

using namespace sqlite3xx;

namespace x2lib
{

	SqliteDB::SqliteDB()
	{
		m_pDB = nullptr;
		memset(m_szFile, 0, sizeof(m_szFile));
	}

	SqliteDB::~SqliteDB(void)
	{
	}

	bool SqliteDB::Init(const char* pszFile, const char* pszPwd)
	{
		if (m_pDB && (!pszFile || 0 == strcmp(m_szFile, pszFile)))
		{
			return true;
		}

		if (m_pDB)
		{
			sqlite3xx_close(m_pDB);
		}

		if (pszFile)
		{
			strcpy(m_szFile, pszFile);
		}
		else
		{
			strcpy(m_szFile, ":memory:");
		}

		int nRet = 0;
		if (SQLITE_OK != sqlite3xx_open(m_szFile, &m_pDB))
		{
			m_pDB = nullptr;
			return false;
		}

#if 1 // 仅供SqlCipher使用
		if (pszPwd && pszPwd[0] && SQLITE_OK != sqlite3xx_key(m_pDB, pszPwd, strlen(pszPwd)))
		{
			sqlite3xx_close(m_pDB);
			m_pDB = nullptr;
			return false;
		}
#endif

		return true;
	}

	void SqliteDB::UnInit()
	{
		if (m_pDB)
		{
			sqlite3xx_close(m_pDB);
			m_pDB = nullptr;
		}
	}

	const char* SqliteDB::GetFile()
	{
		return m_szFile[0] ? m_szFile : nullptr;
	}

	bool SqliteDB::InsertItem(const char *pszTable, ...)
	{
		va_list body;
		va_start(body, pszTable);
        bool isSucc = insertItem(pszTable, body);
		va_end(body);
		return isSucc;
	}

    bool SqliteDB::insertItem(const char *pszTable, va_list va)
    {
        char szKeys[1024] = { 0 };
        char* pszValues = (char*)calloc(1, 1024 * 8);

        for (int i = 0;; ++i)
        {
            char* pkv = va_arg(va, char*);
            if (pkv == nullptr) { break; }
            sprintf(szKeys, "%s,%s", szKeys, &pkv[2]);
            if (pkv[0] == 'I' || pkv[0] == 'B')
            {
                sprintf(pszValues, "%s,%d", pszValues, va_arg(va, int));
            }
            else if (pkv[0] == 'F')
            {
                sprintf(pszValues, "%s,%f", pszValues, va_arg(va, double));
            }
            else if (pkv[0] == 'S')
            {
                sprintf(pszValues, "%s,'%s'", pszValues, va_arg(va, char*));
            }
        }

        char* pszSql = (char*)calloc(1, 1024 * 10);
        if (pszValues[0] && szKeys[0])
        {
            sprintf(pszSql, "INSERT INTO %s (%s) VALUES (%s);", pszTable, &szKeys[1], &pszValues[1]);
        }
        else
        {
            sprintf(pszSql, "INSERT INTO %s DEFAULT VALUES;", pszTable);
        }
        bool isSucc = ExecuteSql(pszSql);
        free(pszValues);
        free(pszSql);
        return isSucc;
    }

	//bool SqliteDB::CreateTable(const char* pszTable, ...)
	//{
	//	char szCols[1024] = { 0 };

	//	va_list body;
	//	va_start(body, pszTable);
	//	for (int i = 0;; ++i)
	//	{
	//		char* pkv = va_arg(body, char*);
	//		if (pkv == nullptr) { break; }
	//		if (pkv[0] == 'I' || pkv[0] == 'B')
	//		{
	//			sprintf(szCols, "%s INT", szCols, &pkv[2]);
	//		}
	//		else if (pkv[0] == 'F')
	//		{
	//			sprintf(szCols, "%s REAL", szCols, &pkv[2]);
	//		}
	//		else if (pkv[0] == 'S')
	//		{
	//			sprintf(szCols, "%s TEXT", szCols, &pkv[2]);
	//		}
	//	}
	//	va_end(body);

	//	char szSql[1024] = { 0 };
	//	sprintf(szSql, "CREATE TABLE %s(%s);", pszTable, szCols);
	//	return ExecuteSql(szSql);
	//}

	int SqliteDB::SelectItems(const char* pszTable, bool coMethod, int(*callback)(void*, int, char**, char**), ...)
	{
        va_list va;
        va_start(va, callback);
        int nRow = selectItems(pszTable, coMethod, callback, va);
        va_end(va);

        return nRow;
	}

    int SqliteDB::selectItems(const char* pszTable, bool coMethod, int(*callback)(void*, int, char**, char**), va_list va)
    {
        int nCount = 0;

        if (!m_pDB) { return 0; }

        const char* pszCoMethod = nullptr;
        if (coMethod) { pszCoMethod = "OR "; }
        else { pszCoMethod = "AND "; }

        char* pszWhere = (char*)calloc(1, 1024 * 8);
        for (int i = 0;; ++i)
        {
            char* pkv = va_arg(va, char*);
            if (pkv == nullptr) { break; }
            if (pkv[0] == 'I' || pkv[0] == 'B')
            {
                sprintf(pszWhere, "%s%s=%d ", pszWhere, &pkv[2], va_arg(va, int));
            }
            else if (pkv[0] == 'F')
            {
                sprintf(pszWhere, "%s%s=%f ", pszWhere, &pkv[2], va_arg(va, double));
            }
            else if (pkv[0] == 'S')
            {
                sprintf(pszWhere, "%s%s='%s' ", pszWhere, &pkv[2], va_arg(va, char*));
            }

            strcat(pszWhere, pszCoMethod);
        }

        pszWhere[strlen(pszWhere) - strlen(pszCoMethod)] = 0;

        char szSql[1024] = { 0 };
        sprintf(szSql, "SELECT COUNT(*) FROM %s WHERE %s;", pszTable, pszWhere);
        //m_mutex.Lock();
        sqlite3xx_exec(m_pDB, szSql, [](void* pVoid, int, char**, char**)->int {
            ++(*(int*)pVoid);
            return 0;
        }, &nCount, nullptr);
		//m_mutex.Unlock();

        if (callback)
        {
            sprintf(szSql, "SELECT * FROM %s WHERE %s;", pszTable, pszWhere);
            char *pszErr = nullptr;
			//m_mutex.Lock();
            sqlite3xx_exec(m_pDB, szSql, callback, 0, &pszErr);
            if (pszErr) { sqlite3xx_free(pszErr); }
			//m_mutex.Unlock();
        }

        return nCount;
    }

	int SqliteDB::SelectItems(const char* pszTable, int(*callback)(void* pVoid, int nCount, char** pValue, char** pName), void* pVoid, const char* pszCondition)
	{
		int nCount = 0;

		if (!m_pDB) { return 0; }

		char szSql[1024] = { 0 };
		sprintf(szSql, "SELECT COUNT(*) FROM %s %s;", pszTable, pszCondition);
		//m_mutex.Lock();
		sqlite3xx_exec(m_pDB, szSql, [](void* pVoid, int, char**, char**)->int {
			++(*(int*)pVoid);
			return 0;
		}, &nCount, nullptr);
		//m_mutex.Unlock();

		if (callback)
		{
			sprintf(szSql, "SELECT * FROM %s %s;", pszTable, pszCondition);
			char* pszErr = nullptr;
			//m_mutex.Lock();
			sqlite3xx_exec(m_pDB, szSql, callback, pVoid, &pszErr);
			if (pszErr) { sqlite3xx_free(pszErr); }
			//m_mutex.Unlock();
		}

		return nCount;
	}

	bool SqliteDB::IsTableExist(const char* szTable)
	{
		char *pErrmsg = nullptr;
		char szSql[512] = { 0 };
		int  nRow = 0;
		int nColumn = 0;
		char **pDBResult = nullptr;

		sprintf(szSql, "SELECT * FROM sqlite_master WHERE type='table' AND name = '%s';", szTable);
		//m_mutex.Lock();
		int nRet = sqlite3xx_get_table(m_pDB, szSql, &pDBResult, &nRow, &nColumn, &pErrmsg);
		if (pDBResult) { sqlite3xx_free_table(pDBResult); }
		if (pErrmsg) { sqlite3xx_free(pErrmsg); }
		//m_mutex.Unlock();

	    return (nRet == SQLITE_OK && nRow > 0);
	}

	//表字段是否存在
	bool SqliteDB::IsFiledExist(const char* pszTable, char* pszFiled)
	{
		char*	pErrmsg = nullptr;
		char	szSql[1024] = { 0 };
		int		nRow = 0;
		int nColumn = 0;
		char **pDBResult = nullptr;

		sprintf(szSql, "SELECT sql FROM sqlite_master WHERE tbl_name='%s' and type='table';", pszTable);
		//m_mutex.Lock();
		int nRet = sqlite3xx_get_table(m_pDB, szSql, &pDBResult, &nRow, &nColumn, &pErrmsg);
		if (pErrmsg) { sqlite3xx_free(pErrmsg); }
		//m_mutex.Unlock();

		bool bFind = false;
		do
		{
			if (nRet != SQLITE_OK || nRow <= 0 || pDBResult == nullptr)
				break;

			char* pColumValue = pDBResult[1];
			if (pColumValue == nullptr)
				break;

			if (nullptr == strstr(pColumValue, pszFiled))
				break;

			bFind = true;
		} while (0);
		sqlite3xx_free_table(pDBResult);
		return bFind;
	}

	bool SqliteDB::AppendFiled(const char* pszTable, const char* pszFiled, const char* pszKeyType)
	{
		char	szSql[1024] = { 0 };
		sprintf(szSql, "ALTER TABLE '%s' ADD '%s' %s;", pszTable, pszFiled, pszKeyType);
		return ExecuteSql(szSql);
	}

	bool SqliteDB::DropTable(const char* pszTable)     //删除表
	{
		char	szSql[1024] = { 0 };
		sprintf(szSql, "DROP TABLE '%s';", pszTable);
		return ExecuteSql(szSql);
	}

	bool SqliteDB::CleanTable(const char* pszTable)    //清空表
	{
		char	szSql[1024] = { 0 };
		sprintf(szSql, "DELETE FROM '%s';", pszTable);
		return ExecuteSql(szSql);
	}

	bool SqliteDB::UpgradeTable(const char* pszTable, const char* pszCreateSql)
	{
		char	szSql[1024] = { 0 };
		char szTableBak[32];
		sprintf(szTableBak, "%s_bak", pszTable);
		sprintf(szSql, "ALTER TABLE '%s' RENAME TO '%s';", pszTable, szTableBak);
		if (ExecuteSql(szSql))
		{
			ExecuteSql(pszCreateSql);

			sprintf(szSql, "PRAGMA table_info(%s)", pszTable);
			typedef std::tuple<std::set<std::string>, std::set<std::string>> SetCols;
			SetCols setCols;
			ExecuteSql(szSql, [](void* pVoid, int nCount, char** pValue, char** pName)->int {
				SetCols& setCols = *(SetCols*)pVoid;
				auto& cols = std::get<0>(setCols);
				cols.insert(pValue[1]);
				return 0;
			}, &setCols);
			sprintf(szSql, "PRAGMA table_info(%s)", szTableBak);
			ExecuteSql(szSql, [](void* pVoid, int nCount, char** pValue, char** pName)->int {
				SetCols& setCols = *(SetCols*)pVoid;
				auto& cols0 = std::get<0>(setCols);
				auto& cols1 = std::get<1>(setCols);
				if (1 == cols0.count(pValue[1]))
				{
					cols1.insert(pValue[1]);
				}
				return 0;
			}, &setCols);

			auto& cols0 = std::get<0>(setCols);
			auto& cols1 = std::get<1>(setCols);

			if (cols0 != cols1)
			{
				char szColumns[512] = { 0 };
				for (auto& it : cols1)
				{
					sprintf(szColumns, "%s,%s", szColumns, it.c_str());
				}
				sprintf(szSql, "INSERT INTO %s (%s) SELECT %s FROM %s;", pszTable, &szColumns[1], &szColumns[1], szTableBak);
				ExecuteSql(szSql);
				DropTable(szTableBak);
			}
			else
			{
				DropTable(pszTable);
				sprintf(szSql, "ALTER TABLE '%s' RENAME TO '%s';", szTableBak, pszTable);
				ExecuteSql(szSql);
			}

			return true;
		}
		
		return false;
	}

	bool SqliteDB::ExecuteSql(const char* szSql, int(*callback)(void*, int, char**, char**), void* p)
	{
		char * pErrmsg = nullptr;
		//m_mutex.Lock();
		int  iRet = sqlite3xx_exec(m_pDB, szSql, callback, p, &pErrmsg);
		if (pErrmsg) { sqlite3xx_free(pErrmsg); }
		//m_mutex.Unlock();
		return (iRet == SQLITE_OK);
	}

	bool SqliteDB::ExecuteSql(int(*callback)(void*, int, char**, char**), const char* pszSqlFmt, ...)
	{
		char* pszSql = (char*)calloc(1, 1024 * 10);
		va_list body;
		va_start(body, pszSqlFmt);
		vsprintf(pszSql, pszSqlFmt, body);
		va_end(body);
		bool isSucc = ExecuteSql(pszSql, callback);
		free(pszSql);
		return isSucc;
	}

	bool SqliteDB::BeginTransaction()
	{
		return (SQLITE_OK == sqlite3xx_exec(m_pDB, "begin", nullptr, nullptr, nullptr));
	}

	bool SqliteDB::RollbackTransaction()
	{
		return (SQLITE_OK == sqlite3xx_exec(m_pDB, "rollback", nullptr, nullptr, nullptr));
	}

	bool SqliteDB::CommitTransaction()
	{
		return (SQLITE_OK == sqlite3xx_exec(m_pDB, "commit", nullptr, nullptr, nullptr));
	}

	bool SqliteDB::Convert2(sqlite3db* pMemDB, const char* pszFile, bool toFile)
	{
        sqlite3db *pFileDB = nullptr;
		if (SQLITE_OK == sqlite3xx_open(pszFile, &pFileDB))
		{
            sqlite3db *pFrom = (toFile ? pMemDB : pFileDB);
            sqlite3db *pTo = (toFile ? pFileDB : pMemDB);

			sqlite3xx_backup *pBackup = sqlite3xx_backup_init(pTo, "main", pFrom, "main");
			if (pBackup)
			{
				sqlite3xx_backup_step(pBackup, -1);
                sqlite3xx_backup_finish(pBackup);
			}

			bool isSucc = (SQLITE_OK == sqlite3xx_errcode(pFileDB));
			sqlite3xx_close(pFileDB);
			return isSucc;
		}
		return false;
	}

}

